<?php

namespace Admin\Model;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Where;
use Admin\Entity\Reader;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;
use Zend\Db\Adapter\Adapter;

class ReaderTable extends AbstractTableGateway {

    protected $table;
    protected $adapter;
    protected $resultSetPrototype;

    public function __construct(Adapter $adapter) {
        $this->table = 'lib_readers';
        $this->adapter = $adapter;
        $this->resultSetPrototype = new ResultSet();
    }

    public function fetchAllReader($paginated = false, $value = 4) {
        if ($paginated) {
            $sql = new Sql($this->adapter);
            $select = $sql->select();
            $select->from(array('r' => $this->table))
                    ->join(array('u' => 'lib_users'), 'u.userID = r.userID', array('fullName'))
                    ->join(array('c' => 'tbl_class'), 'r.classID = c.classID', array('className'))
                    ->join(array('f' => 'tbl_faculties'), 'f.facultyID = c.facultyID', array('facultyName'));
            $where = new Where();
            $where->equalTo('u.roleID', $value);
            $select->where($where);

            $resultSetPrototype = new ResultSet();
            $paginatorAdapter = new DbSelect($select, $this->getAdapter(), $resultSetPrototype);
            $paginator = new Paginator($paginatorAdapter);
            return $paginator;
        }
    }

    public function getAllNewReader() {
        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from(array('r' => $this->table))
                ->join(array('u' => 'lib_users'), 'u.userID = r.userID')
                ->join(array('c' => 'tbl_class'), 'r.classID = c.classID', array('className'))
                ->join(array('f' => 'tbl_faculties'), 'f.facultyID = c.facultyID', array('facultyName'));
        $where = new Where();
        // 0 đại diện cho bạn đọc mới đăng ký
        $where->equalTo('r.isActive', 0);
        $select->where($where);
        $statement = $sql->prepareStatementForSqlObject($select);
        $result = $statement->execute();
        $resultSet = clone $this->resultSetPrototype;
        $resultSet->initialize($result);
        $resultSet->buffer();
        $resultSet->next();
        return $resultSet;
    }

    public function getReader($cardBarcode) {
        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from(array('r' => $this->table));
        $where = new Where();
        $where->equalTo('r.cardBarcode', $cardBarcode);
        $select->where($where);
        $statement = $sql->prepareStatementForSqlObject($select);
        $result = $statement->execute();
        $row = $result->current();
        return $row;
    }

    public function getInfoReader($carBarcode) {
        try {
            $sql = new Sql($this->adapter);
            $select = $sql->select();
            $select->from(array('r' => $this->table))
                    ->join(array('u' => 'lib_users'), 'u.userID = r.userID')
                    ->join(array('c' => 'tbl_class'), 'r.classID = c.classID', array('className'))
                    ->join(array('f' => 'tbl_faculties'), 'f.facultyID = c.facultyID', array('facultyName'));
            $where = new Where();
            $where->equalTo('r.cardBarcode', $carBarcode);
            $select->where($where);
            $statement = $sql->prepareStatementForSqlObject($select);
            $result = $statement->execute();
            $row = $result->current();
            return $row;
        } catch (Exception $e) {
            throw new \Exception("Could not excuteQuery getInfoReader()");
        }
    }

    public function getInfoReaderFromUserID($userID) {
        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from(array('r' => $this->table))
                ->join(array('u' => 'lib_users'), 'u.userID = r.userID')
                ->join(array('c' => 'tbl_class'), 'r.classID = c.classID', array('className'))
                ->join(array('f' => 'tbl_faculties'), 'f.facultyID = c.facultyID', array('facultyName'));
        $where = new Where();
        $where->equalTo('r.userID', $userID);
        $select->where($where);
        $statement = $sql->prepareStatementForSqlObject($select);
        $result = $statement->execute();
        $row = $result->current();
        return $row;
    }

    // 
    public function searchReaderName($nameReader) {
        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->columns(array('cardBarcode', 'dateCreated', 'dateExpiry', 'totalBorrow', 'isActive'))
                ->from(array('r' => $this->table))
                ->join(array('u' => 'lib_users'), 'u.userID = r.userID', array('fullName', 'birthday'))
                ->join(array('c' => 'tbl_class'), 'r.classID = c.classID', array('className'))
                ->join(array('f' => 'tbl_faculties'), 'f.facultyID = c.facultyID', array('facultyName'));
        $where = new Where();
        $where->like('u.fullName', '%' . $nameReader . '%');
        $select->where($where);
        $statement = $sql->prepareStatementForSqlObject($select);
        $result = $statement->execute();
        $resultSet = clone $this->resultSetPrototype;
        $resultSet->initialize($result);
        $resultSet->buffer();
        $resultSet->next();
        return $resultSet;
    }

    // 
    public function searchReaderBarcode($cardBarcode) {
        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->columns(array('cardBarcode', 'dateCreated', 'dateExpiry', 'totalBorrow', 'isActive'))
                ->from(array('r' => $this->table))
                ->join(array('u' => 'lib_users'), 'u.userID = r.userID', array('fullName', 'birthday'))
                ->join(array('c' => 'tbl_class'), 'r.classID = c.classID', array('className'))
                ->join(array('f' => 'tbl_faculties'), 'f.facultyID = c.facultyID', array('facultyName'));
        $where = new Where();
        $where->equalTo('cardBarcode', $cardBarcode);
        $select->where($where);
        $statement = $sql->prepareStatementForSqlObject($select);
        $result = $statement->execute();
        $resultSet = clone $this->resultSetPrototype;
        $resultSet->initialize($result);
        $resultSet->buffer();
        $resultSet->next();
        return $resultSet;
    }

    public function getCirculationPolicy($policyID) {
        try {
            $sql = new Sql($this->adapter);
            $select = $sql->select();
            $select->from(array('c' => 'lib_ciculationpolicies'));
            $where = new Where();
            $where->equalTo('c.policyID', $policyID);
            $select->where($where);
            $statement = $sql->prepareStatementForSqlObject($select);
            $result = $statement->execute();
            $row = $result->current();
            return $row;
        } catch (Exception $e) {
            throw new \Exception("Could not excuteQuery getInfoReader()");
        }
    }

    /*
     * insert Reader into lib_readers
     * @return boolean
     */

    public function saveReader(Reader $reader) {
        $data = array(
            'cardBarcode' => $reader->cardBarcode,
            'dateCreated' => $reader->dateCreated,
            'dateExpiry' => $reader->dateExpiry,
            'yearLearn' => $reader->yearLearn,
            'isActive' => $reader->isActive,
            'classID' => $reader->classID,
            'userID' => $reader->userID,
        );
        try {
            $this->insert($data);
            return true;
        } catch (Exception $e) {
            echo "loi insert reader roi";
        }
        return false;
    }
     public function updateReaderInfo(Reader $reader, $userID) {
        $data = array(
            'cardBarcode' => $reader->cardBarcode,
            'dateCreated' => $reader->dateCreated,
            'dateExpiry' => $reader->dateExpiry,
            'yearLearn' => $reader->yearLearn,
            'isActive' => $reader->isActive,
            'classID' => $reader->classID,
        );
        $this->update($data, array('userID' => $userID));
    }
    public function updateTotalBorrow($cardBarcode, $totalBorrow) {
        $this->update(array('totalBorrow' => $totalBorrow), array('cardBarcode' => $cardBarcode));
    }

    public function changeIsActiveStatus($isActive, $userID) {
        $this->update(array('isActive' => $isActive), array('userID' => $userID));
    }

}

?>
